<?php
namespace app\index\controller;

use app\index\model\JwSchool;
use PHPExcel;
use PHPExcel_IOFactory;
use app\user\model\User;
class Excel extends Base
{
  
    //execl模板下载
    public function template_download()
    {
        $data=$this->request->param();
        $objExcel = new PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $objActSheet = $objExcel->getActiveSheet(0);
        // $objActSheet->setTitle('template'); //设置excel的标题
        $objActSheet->setCellValue('A1', 'id');
        $objActSheet->setCellValue('B1', '姓名');
        $objActSheet->setCellValue('C1', '手机号');
        $objActSheet->setCellValue('D1', '年龄');
        $objActSheet->setCellValue('E1', '预约单位');
        $objActSheet->setCellValue('F1', '学校');
        $objActSheet->setCellValue('G1', '备注');
        $objActSheet->setCellValue('H1', '预约时间');
        $objActSheet->setCellValue('I1', '是否带家人一起接种');
        $objActSheet->setCellValue('J1', '是否参加宫颈癌筛查');
        $baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
        //默认数据
        $user = new User();
        $explame_data = $user->where("type",$data['id'])->select();

        foreach ($explame_data as $key => $value) {
            $i = $baseRow + $key;
            $objExcel->getActiveSheet()->setCellValue('A' . $i, $value['id']);
            $objExcel->getActiveSheet()->setCellValue('B' . $i, $value['name']);
            $objExcel->getActiveSheet()->setCellValue('C' . $i, $value['tel']);
            $objExcel->getActiveSheet()->setCellValue('D' . $i, $value['age']);
            $objExcel->getActiveSheet()->setCellValue('E' . $i, $value['unit']);
            $objExcel->getActiveSheet()->setCellValue('F' . $i, $value['school']);
            $objExcel->getActiveSheet()->setCellValue('G' . $i, $value['remark']);
            $objExcel->getActiveSheet()->setCellValue('H' . $i, $value['cTime']);
            $objExcel->getActiveSheet()->setCellValue('I' . $i, $value['is_home']);
            $objExcel->getActiveSheet()->setCellValue('J' . $i, $value['is_join']);
        }


        $objExcel->setActiveSheetIndex(0);
        //4、输出
        $objExcel->setActiveSheetIndex();
        header('Content-Type: applicationnd.ms-excel');
        $time = date('Y-m-d H:i:s');
        header("Content-Disposition: attachment;filename=用户数据" . $time . ".xls");
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }

    public function import_batch_send()
    {
        header("content-type:text/html;charset=utf-8");

        //上传excel文件
        $file = request()->file('file');
        //将文件保存到public/uploads目录下面
        $info = $file->validate(['size' => 1048576, 'ext' => 'xls,xlsx'])->move('static/excel/');
        if ($info) {
            //获取上传到后台的文件名
            $fileName = $info->getSaveName();
            //获取文件路径
            $filePath = "static/excel/". $fileName;
            // dump($fileName);
            // dump($filePath);
            //获取文件后缀
            $suffix = $info->getExtension();
            //判断哪种类型
            if ($suffix == "xlsx") {
                $reader = \PHPExcel_IOFactory::createReader('Excel2007');
            } else {
                $reader = PHPExcel_IOFactory::createReader('Excel5');
            }
        } else {
            return json(['status' => '1', 'message' => '文件过大或格式不正确导致上传失败-_-!']);
        }
        // //载入excel文件
        $excel = $reader->load($filePath, $encode = 'utf-8');
        // //读取第一张表
        $sheet = $excel->getSheet(0);
        // //获取总行数
        $row_num = $sheet->getHighestRow();
        // //获取总列数
        $col_num = $sheet->getHighestColumn();
        $import_data = []; //数组形式获取表格数据
        for ($i = 2; $i <= $row_num; $i++) { 
            $import_data[$i]['idcard']  = $sheet->getCell("A" . $i)->getValue();
            $import_data[$i]['t_id']  = $sheet->getCell("B" . $i)->getValue();
            $import_data[$i]['name']  = $sheet->getCell("C" . $i)->getValue();
            $import_data[$i]['tel']  = $sheet->getCell("D" . $i)->getValue();
            $import_data[$i]['nation']  = $sheet->getCell("E" . $i)->getValue();
            $import_data[$i]['gender']  = $sheet->getCell("F" . $i)->getValue();
            $import_data[$i]['age']  = $sheet->getCell("G" . $i)->getValue();
            $import_data[$i]['height']  = $sheet->getCell("H" . $i)->getValue();
            $import_data[$i]['weight']  = $sheet->getCell("I" . $i)->getValue();
            $import_data[$i]['waistline']  = $sheet->getCell("J" . $i)->getValue();
            $import_data[$i]['community']  = $sheet->getCell("K" . $i)->getValue();
            $import_data[$i]['address']  = $sheet->getCell("L" . $i)->getValue();
            $import_data[$i]['marry']  = $sheet->getCell("M" . $i)->getValue();
            $import_data[$i]['edu_rank']  = $sheet->getCell("N" . $i)->getValue();
            $import_data[$i]['xy_ssy']  = $sheet->getCell("O" . $i)->getValue();
            $import_data[$i]['xy_szy']  = $sheet->getCell("P" . $i)->getValue();
            $import_data[$i]['xuetang_k']  = $sheet->getCell("Q" . $i)->getValue();
            $import_data[$i]['xuetang_s']  = $sheet->getCell("R" . $i)->getValue();
            $import_data[$i]['heart']  = $sheet->getCell("S" . $i)->getValue();
            $import_data[$i]['danguchun']  = $sheet->getCell("T" . $i)->getValue();
            $import_data[$i]['ganyousanz']  = $sheet->getCell("U" . $i)->getValue();
            $import_data[$i]['xqdmd']  = $sheet->getCell("V" . $i)->getValue();
            $import_data[$i]['xqgmd']  = $sheet->getCell("W" . $i)->getValue();
            $import_data[$i]['person_health']  = $sheet->getCell("X" . $i)->getValue();
            $import_data[$i]['father_history']  = $sheet->getCell("Y" . $i)->getValue();
            $import_data[$i]['mother_history']  = $sheet->getCell("Z" . $i)->getValue();
            $import_data[$i]['brothers_history']  = $sheet->getCell("AA" . $i)->getValue();
            $import_data[$i]['kids_history']  = $sheet->getCell("AB" . $i)->getValue();
            $import_data[$i]['confirmdate_gxy']  = $sheet->getCell("AC" . $i)->getValue();
            $import_data[$i]['confirmdate_tnb']  = $sheet->getCell("AD" . $i)->getValue();
            $import_data[$i]['confirmdate_gxb']  = $sheet->getCell("AE" . $i)->getValue();
            $import_data[$i]['confirmdate_mxzsxfjb']  = $sheet->getCell("AF" . $i)->getValue();
            $import_data[$i]['confirmdate_exzl']  = $sheet->getCell("AG" . $i)->getValue();
            $import_data[$i]['confirmdate_nzz']  = $sheet->getCell("AH" . $i)->getValue();
            $import_data[$i]['confirmdate_zxjsjb']  = $sheet->getCell("AI" . $i)->getValue();
            $import_data[$i]['confirmdate_jhb']  = $sheet->getCell("AJ" . $i)->getValue();
            $import_data[$i]['confirmdate_gzjb']  = $sheet->getCell("AK" . $i)->getValue();
            $import_data[$i]['confirmdate_xtjx']  = $sheet->getCell("AL" . $i)->getValue();
            $import_data[$i]['diseasetext_zyb']  = $sheet->getCell("AM" . $i)->getValue();
            $import_data[$i]['confirmdate_zyb']  = $sheet->getCell("AN" . $i)->getValue();
            $import_data[$i]['disease_text_qt']  = $sheet->getCell("AO" . $i)->getValue();
            $import_data[$i]['confirmdate_qt']  = $sheet->getCell("AP" . $i)->getValue();
        }

        if (empty($import_data)) {
            return json(['status' => '1', 'message' => '数据解析失败']);
        }
        //校验手机号是否重复
        $phone_array = array_column($import_data, 'tel');
        $phone_ids = implode(',', $phone_array);
        $user = new User();
        $result_phone = $user->field('tel')->where('tel', 'in', $phone_ids)
            ->select()->toArray();
        $newArr = filterArray($import_data, $result_phone);
        // dump( $newArr);
        // // //将数据保存到数据库
        $res = $user->insertAll($newArr);
        if ($res) {
            return json(['status' => '2', 'message' => '导入成功']);
        } else {
            return json(['status' => '1', 'message' => '提交全部为重复数据，请刷新重试']);
        }
   
    }





    //class_execl模板下载
    public function class_download()
    {
        $objExcel = new PHPExcel();
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('template'); //设置excel的标题
        $objActSheet->setCellValue('A3', '周次');
        $objActSheet->setCellValue('B3', '星期');
        $objActSheet->setCellValue('C3', '日期');
        $objActSheet->setCellValue('D3', '节次');
        $objActSheet->setCellValue('E3', '课程名称');
        $objActSheet->setCellValue('F3', '授课内容');
        $objActSheet->setCellValue('G3', '主讲老师');
        $objActSheet->setCellValue('H3', '职称');
        $objActSheet->setCellValue('I3', '授课班级');
        $objActSheet->setCellValue('J3', '教室');
        $objActSheet->setCellValue('K3', '授课方式');
        $objActSheet->setCellValue('L3', '所在临床科室');
        $objActSheet->setCellValue('M3', '所属教研室');
        $baseRow = 4; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
        //默认数据
        // $explame_data = array(
        //     array(
        //         'user_id' => '1',
        //         'nickname' => '小明',
        //         'phone' => '15012345678',
        //     ),
        // );

        // foreach ($explame_data as $key => $value) {
        //     $i = $baseRow + $key;
        //     $objExcel->getActiveSheet()->setCellValue('A' . $i, $value['user_id']);
        //     $objExcel->getActiveSheet()->setCellValue('B' . $i, $value['nickname']);
        //     $objExcel->getActiveSheet()->setCellValue('C' . $i, $value['phone']);
        // }


        $objExcel->setActiveSheetIndex(0);
        //4、输出
        $objExcel->setActiveSheetIndex();
        header('Content-Type: applicationnd.ms-excel');
        $time = date('Y-m-d');
        header("Content-Disposition: attachment;filename=导入模板" . $time . ".xls");
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }

    //上传到class表单
    public function import_class()
    {
        header("content-type:text/html;charset=utf-8");

        //上传excel文件
        $file = request()->file('file');
        //将文件保存到public/uploads目录下面
        $info = $file->validate(['size' => 1048576, 'ext' => 'xls,xlsx'])->move('static/excel/');
        if ($info) {
            //获取上传到后台的文件名
            $fileName = $info->getSaveName();
            //获取文件路径
            $filePath = "static/excel/". $fileName;
            // dump($fileName);
            // dump($filePath);
            //获取文件后缀
            $suffix = $info->getExtension();
            //判断哪种类型
            if ($suffix == "xlsx") {
                $reader = \PHPExcel_IOFactory::createReader('Excel2007');
            } else {
                $reader = PHPExcel_IOFactory::createReader('Excel5');
            }
        } else {
            return json(['status' => '1', 'message' => '文件过大或格式不正确导致上传失败-_-!']);
        }
        // //载入excel文件
        $excel = $reader->load($filePath, $encode = 'utf-8');
        // //读取第一张表
        $sheet = $excel->getSheet(0);
        // //获取总行数
        $row_num = $sheet->getHighestRow();
        // //获取总列数
        $col_num = $sheet->getHighestColumn();
        $import_data = []; //数组形式获取表格数据
        for ($i = 4; $i <= $row_num; $i++) { 
            $import_data[$i]['week']  = $sheet->getCell("A" . $i)->getValue();
            $import_data[$i]['weekend']  = $sheet->getCell("B" . $i)->getValue();
            $import_data[$i]['class_date']  = $sheet->getCell("C" . $i)->getValue();
            $import_data[$i]['section']  = $sheet->getCell("D" . $i)->getValue();
            $import_data[$i]['class_name']  = $sheet->getCell("E" . $i)->getValue();
            $import_data[$i]['class_content']  = $sheet->getCell("F" . $i)->getValue();
            $import_data[$i]['teacher']  = $sheet->getCell("G" . $i)->getValue();
            $import_data[$i]['teacher_title']  = $sheet->getCell("H" . $i)->getValue();
            $import_data[$i]['class_class']  = $sheet->getCell("I" . $i)->getValue();
            $import_data[$i]['class_room']  = $sheet->getCell("J" . $i)->getValue();
            $import_data[$i]['teacher_way']  = $sheet->getCell("K" . $i)->getValue();
            $import_data[$i]['department']  = $sheet->getCell("L" . $i)->getValue();
            $import_data[$i]['research_section']  = $sheet->getCell("M" . $i)->getValue();
        }

        if (empty($import_data)) {
            return json(['status' => '1', 'message' => '数据解析失败']);
        }
        // //校验手机号是否重复
        // $phone_array = array_column($import_data, 'tel');
        // $phone_ids = implode(',', $phone_array);
        $JwSchool = new JwSchool();
        // $result_phone = $JwSchool->field('tel')->where('tel', 'in', $phone_ids)
        //     ->select()->toArray();
        // $newArr = filterArray($import_data, $result_phone);
        // dump( $newArr);
        // // //将数据保存到数据库
        $res = $JwSchool->insertAll($import_data);
        if ($res) {
            return json(['status' => '2', 'message' => '导入成功']);
        } else {
            return json(['status' => '1', 'message' => '提交全部为重复数据，请刷新重试']);
        }
   
    }
       
}